# -*- coding: utf-8 -*-
# @Time : 2021/12/6 15:43
# @Author : Limusen
# @File : excel_utils


import os
import xlrd3

excel_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', 'testcase_data', 'testcase_infos.xlsx')


class ExcelUtils:

    def __init__(self, excel_file_path, sheet_name):
        self.excel_file_path = excel_file_path
        self.sheet_name = sheet_name
        # 可以在构造方法中调用实例化的方法
        self.sheet_obj = self.get_sheet_object()

    def get_sheet_object(self):
        """
        创建文件对象
        :return:
        """
        workbook_obj = xlrd3.open_workbook(self.excel_file_path)
        sheet_obj = workbook_obj.sheet_by_name(self.sheet_name)
        return sheet_obj

    def get_row_count(self):
        """
        获取行总数
        :return:
        """
        row_count = self.sheet_obj.nrows
        return row_count

    def get_col_count(self):
        """
        获取列总数
        :return:
        """
        col_count = self.sheet_obj.ncols
        return col_count

    def get_cell_value_by_merged_cells(self, row_index, col_index):
        """
        获取合并单元格的内容
        :param row_index:
        :param col_index:
        :return:
        """
        for (min_row, max_row, min_col, max_col) in self.sheet_obj.merged_cells:
            if row_index >= min_row and row_index < max_row:
                if col_index >= min_col and col_index < max_col:
                    # 是合并单元格,单元格的值等于 合并单元格中的第一个单元格的值
                    cell_value = self.sheet_obj.cell_value(min_row, min_col)
                    break
                else:
                    cell_value = self.sheet_obj.cell_value(row_index, col_index)
            else:
                cell_value = self.sheet_obj.cell_value(row_index, col_index)
        return cell_value

    def get_all_excel_data(self):
        excel_data_list = []
        """
        ex: [{...},{...},{...}]
        获取excel全部数据
        :return:
        """
        for row_num in range(1, self.get_row_count()):  # 不获取表头数据
            row_dict = {}
            for col_num in range(self.get_col_count()):
                row_dict[self.get_cell_value_by_merged_cells(0, col_num)] = self.get_cell_value_by_merged_cells(row_num,
                                                                                                                col_num)
            excel_data_list.append(row_dict)
        return excel_data_list


if __name__ == '__main__':
    ex = ExcelUtils(excel_path, "Sheet1")
    # 获取单行数据
    # print(ex.get_cell_value_by_merged_cells(0, 1))
    # 获取全部数据
    # print(ex.get_all_excel_data())
    print(ex.get_row_count())
    print(ex.get_col_count())
    print(ex.get_all_excel_data())